﻿///***************************************************************
/// 시  스  템 : Updms.Fit.Dac
/// 단위시스템 : FIT
/// 계      층 : Data Access Layer
/// 프로그램명 : Fit_Dac_UPDMS_FIT_REPORT_Fit2010r
/// 파  일  명 : Fit_Dac_UPDMS_FIT_REPORT_Fit2010r.cs
/// 설      명 : 트레이닝분석표
///***************************************************************
/// 수정ID 일자       이름     내용
///---------------------------------------------------------------
/// CH001  2015-01-19 송시명   최초 작성
///***************************************************************
using System;
using System.Data;
using System.EnterpriseServices;
using System.Reflection;
using System.Runtime.InteropServices;

using SMFramework.Data;
using System.Text;
using SMFramework.Utility;

namespace Updms.Fit.Dac
{
	[ComVisible(true)]
	[Transaction(TransactionOption.Supported)]
	[JustInTimeActivation(true)]
	public class Fit_Dac_UPDMS_FIT_REPORT_Fit2010r : Base.AppFramework.DacBase
	{
		public Fit_Dac_UPDMS_FIT_REPORT_Fit2010r()
		{
		}

		#region NT [GetRegionalWeightTrainingAnalysisList]
		/// <summary>
		/// 트레이닝분석표
		/// </summary>
		/// <param name="lo_dp"></param>
		/// <returns></returns>
		[AutoComplete]
		public DataSet GetRegionalWeightTrainingAnalysisList(DataPack lo_dp)
		{
			string ls_curr_method
				= MethodBase.GetCurrentMethod().DeclaringType.ToString() + " - " + MethodBase.GetCurrentMethod().ToString();

			StringBuilder sb = new StringBuilder();

			string ls_search_type = lo_dp["ls_search_type"].ToString();
			string ls_base_from_ym = lo_dp["ls_base_from_ym"].ToString();
			string ls_base_to_ym = lo_dp["ls_base_to_ym"].ToString();
			string ls_training_part = lo_dp["ls_training_part"].ToString();
			string ls_training_type_id = lo_dp["ls_training_type_id"].ToString();
			string ls_lang_set = lo_dp["ls_lang_set"].ToString();

			string monthList = DateUtil.GetDiffMonthList(ls_base_from_ym, ls_base_to_ym);
			string[] monthLists = monthList.Split(',');
			string ls_total_amt = "";
			string in_query = string.Empty;

			sb.Append("DECLARE @ls_base_from_ym nvarchar(7),\n");
			sb.Append("        @ls_base_to_ym nvarchar(7),\n");
			sb.Append("        @ls_training_part nvarchar(max),\n");
			sb.Append("        @ls_training_type_id nvarchar(max),\n");
			sb.Append("        @ls_lang_set nvarchar(2)\n\n");
			sb.Append("SET @ls_base_from_ym = '" + ls_base_from_ym + "'\n");
			sb.Append("SET @ls_base_to_ym = '" + ls_base_to_ym + "'\n");
			sb.Append("SET @ls_training_part = '" + ls_training_part + "'\n");
			sb.Append("SET @ls_training_type_id = '" + ls_training_type_id + "'\n");
			sb.Append("SET @ls_lang_set = '" + ls_lang_set + "'\n");

			sb.Append("DECLARE @training_parts nvarchar(max)\n");
			sb.Append("IF @ls_training_part = ''\n");
			sb.Append("BEGIN\n");
			sb.Append("    SET @training_parts = '%'\n");
			sb.Append("END\n");
			sb.Append("ELSE\n");
			sb.Append("BEGIN\n");
			sb.Append("    SET @training_parts = @ls_training_part\n");
			sb.Append("END\n\n");
			sb.Append("DECLARE @training_type_ids nvarchar(max)\n");
			sb.Append("IF @ls_training_type_id = ''\n");
			sb.Append("BEGIN\n");
			sb.Append("    SET @training_type_ids = '%'\n");
			sb.Append("END\n");
			sb.Append("ELSE\n");
			sb.Append("BEGIN\n");
			sb.Append("    SET @training_type_ids = @ls_training_type_id\n");
			sb.Append("END\n");
			sb.Append("\n");
			sb.Append("SELECT Training_Part,\n");
			sb.Append("       Training_Part_Nm,\n");
			sb.Append("       Training_Type_Id,\n");
			sb.Append("       Training_Nm,\n");

			for (int i = 0; i < monthLists.Length; i++)
			{
				string ym = monthLists[i];

				//컬럼
				sb.Append("       ISNULL([" + ym + "], 0) AS '" + ym + "',\n");

				//총액
				ls_total_amt += "ISNULL([" + monthLists[i] + "], 0)+";

				//PIVOT의 IN절
				in_query += "[" + ym + "],";
			}

			in_query = in_query.Substring(0, in_query.LastIndexOf(","));
			ls_total_amt = "       " + ls_total_amt.Remove(ls_total_amt.Length - 1, 1) + " AS 'Total'";

			sb.Append(ls_total_amt + "\n");
			sb.Append("  FROM (\n");
			sb.Append("       SELECT DISTINCT t.Training_Ym,\n");
			sb.Append("              t.Training_Part,\n");
			sb.Append("              dbo.ufn_UPDMS_Get_Code_Name(t.Training_Part, 'FIT', @ls_lang_set) AS Training_Part_Nm,\n");
			sb.Append("              t.Training_Type_Id,\n");
			sb.Append("              t.Training_Nm,\n");
			sb.Append("              t.View_Order,\n");

			if (ls_search_type == "AC001") //중량
			{
				sb.Append("              Total_Weight\n");
			}
			else
			{
				sb.Append("              Total_Count\n");
			}

			sb.Append("         FROM (\n");
			sb.Append("              SELECT LEFT(A.Training_Dt, 7) AS Training_Ym,\n");
			sb.Append("                     C.Training_Part,\n");
			sb.Append("                     C.Training_Type_Id,\n");
			sb.Append("                     C.Training_Nm,\n");
			sb.Append("                     MAX(C.View_Order) AS View_Order,\n");
			sb.Append("                     SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,\n");
			sb.Append("                     SUM(B.Action_Count * B.Set_Count) AS Total_Count\n");
			sb.Append("                FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)\n");
			sb.Append("                JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id\n");
			sb.Append("                JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id\n");
			sb.Append("               WHERE LEFT(A.Training_Dt, 7) >= @ls_base_from_ym\n");
			sb.Append("                 AND LEFT(A.Training_Dt, 7) <= @ls_base_to_ym\n");
			sb.Append("                 AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))\n");
			sb.Append("                     OR C.Training_Part LIKE @training_parts\n");
			sb.Append("                     )\n");
			sb.Append("                 AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))\n");
			sb.Append("                     OR C.Training_Type_Id LIKE @training_type_ids\n");
			sb.Append("                     )\n");
			sb.Append("               GROUP BY LEFT(A.Training_Dt, 7),\n");
			sb.Append("                        C.Training_Part,\n");
			sb.Append("                        C.Training_Type_Id,\n");
			sb.Append("                        C.Training_Nm\n");
			sb.Append("              --운동부위별 소계\n");
			sb.Append("              UNION ALL\n");
			sb.Append("              SELECT LEFT(A.Training_Dt, 7) AS Training_Ym,\n");
			sb.Append("                     Training_Part,\n");
			sb.Append("                     'BA001' AS Training_Type_Id,\n");
			sb.Append("                     dbo.ufn_UPDMS_Get_Code_Name('BA001', 'FIT', @ls_lang_set) AS Training_Nm,\n");
			sb.Append("                     Training_Part + 'X' AS View_Order,\n");
			sb.Append("                     SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,\n");
			sb.Append("                     SUM(B.Action_Count * B.Set_Count) AS Total_Count\n");
			sb.Append("                FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)\n");
			sb.Append("                JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id\n");
			sb.Append("                JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id\n");
			sb.Append("               WHERE LEFT(A.Training_Dt, 7) >= @ls_base_from_ym\n");
			sb.Append("                 AND LEFT(A.Training_Dt, 7) <= @ls_base_to_ym\n");
			sb.Append("                 AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))\n");
			sb.Append("                     OR C.Training_Part LIKE @training_parts\n");
			sb.Append("                     )\n");
			sb.Append("                 AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))\n");
			sb.Append("                     OR C.Training_Type_Id LIKE @training_type_ids\n");
			sb.Append("                     )\n");
			sb.Append("               GROUP BY LEFT(A.Training_Dt, 7),\n");
			sb.Append("                        C.Training_Part\n");
			sb.Append("              --총계\n");
			sb.Append("              UNION ALL\n");
			sb.Append("              SELECT LEFT(A.Training_Dt, 7) AS Training_Ym,\n");
			sb.Append("                     'BA002' AS Training_Part,\n");
			sb.Append("                     'BA002' AS Training_Type_Id,\n");
			sb.Append("                     '' AS Training_Nm,\n");
			sb.Append("                     '' AS View_Order,\n");
			sb.Append("                     SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,\n");
			sb.Append("                     SUM(B.Action_Count * B.Set_Count) AS Total_Count\n");
			sb.Append("                FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)\n");
			sb.Append("                JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id\n");
			sb.Append("                JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id\n");
			sb.Append("               WHERE LEFT(A.Training_Dt, 7) >= @ls_base_from_ym\n");
			sb.Append("                 AND LEFT(A.Training_Dt, 7) <= @ls_base_to_ym\n");
			sb.Append("                 AND (C.Training_Part IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_parts))\n");
			sb.Append("                     OR C.Training_Part LIKE @training_parts\n");
			sb.Append("                     )\n");
			sb.Append("                 AND (C.Training_Type_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@training_type_ids))\n");
			sb.Append("                     OR C.Training_Type_Id LIKE @training_type_ids\n");
			sb.Append("                     )\n");
			sb.Append("               GROUP BY LEFT(A.Training_Dt, 7)\n");
			sb.Append("              ) t\n");
			sb.Append("       ) pv\n");

			if (ls_search_type == "AC001") //중량
			{
				sb.Append(" PIVOT ( SUM(pv.Total_Weight) FOR pv.Training_Ym IN (" + in_query + ")\n");
			}
			else
			{
				sb.Append(" PIVOT ( SUM(pv.Total_Count) FOR pv.Training_Ym IN (" + in_query + ")\n");
			}

			sb.Append("       ) AS pvt\n");
			sb.Append(" ORDER BY Training_Part, View_Order\n");

			//Base.AppFramework.SMEventLog.WriteDataLog("GetRegionalWeightTrainingAnalysisList", sb.ToString());

			DataSet ds = new DataSet();

			return _agent.Fill(sb.ToString(), "UPDMS_FIT_REPORT", ds, null, CommandType.Text, ls_curr_method);
		}

		#endregion

	}
}
